from django.db import models

from dao.models import MySQL
from comm.models import General

# Create your models here.
class Order(object):
    def save_order_tmp(self,data,list):
        order_data =data['order_date']
        doco =data['doco']
        ship_date=data['ship_date']
        store='10001'
        p_name=list[0]
        reserve=list[1]
        futures =list[2]
        confirm=list[3]
        unit =list[4]

        sql="insert into xb_order_tmp(order_doc,p_name,reserve_num,futures_num,confirm_num,unit,store,order_date,ship_date)VALUES ('"
        sql += doco+"','"+p_name+"','"+reserve+"','"+futures+"','"+confirm+"','"+unit+"','"+store+"','"+order_data+"','"+ship_date+"')"
        try:
            mysql = MySQL()
            conn =mysql.connection_sql()
            cusr = conn.cursor()
            result=cusr.execute(sql)
            conn.commit()
            conn.close()
        except Exception as e:
            e.args[0]
        return result

    #通过Excel 保存门店订单信息
    def insert_order_data(self, list):
        str_date = list[0].split(' ')
        order_date = str_date[0].replace('/', '-')
        str_dat = list[1].split(' ')
        ship_date = str_dat[0].replace('/', '-')
        doco = list[2]
        doc = list[3]
        store = "10001"  # list[4]
        p_code = list[5]
        p_name = list[7]
        unit = list[8]
        type = list[9]
        reserve_num = '0'
        futures_num = '0'
        if list[10] != '':
            reserve_num = str(list[10])

        if list[11] != '':
            futures_num = str(list[11])
        confirm_num = str(list[12])
        receive_num = str(list[13])
        price = str(round(list[14], 2))
        amnt = str(list[15])
        sysdate = General.str_date
        sql = "insert into kalf_order_list(kalf_doco,kalf_doc,order_date,ship_date,kalf_code,kalf_name," \
              "kalf_unit,kalf_type,reserve_num,futures_num,confirm_num,receive_num,kalf_price,kalf_amnt,xibei_store,sys_date)VALUES('"
        sql += doco + "','" + doc + "','" + order_date + "','" + ship_date + "','" + p_code + "','" + p_name + "','" + unit + "','" + type + "','"
        sql += reserve_num + "','" + futures_num + "','" + confirm_num + "','" + receive_num + "','" + price + "','" + amnt + "','" + store + "','" + sysdate + "')"
        try:
            mysql = MySQL()
            conn = mysql.connection_sql()
            cusr = conn.cursor()
            result = cusr.execute(sql)
            conn.commit()
            conn.close()
        except Exception as e:
            raise e.args[1]
        return result

    def show_order_list(self):
        sql = "select order_id,order_doc,p_name,reserve_num,futures_num,confirm_num,unit,order_date,ship_date,store from xb_order_tmp "

        try:
            mysql = MySQL()
            conn = mysql.connection_sql()
            cusr = conn.cursor()
            cusr.execute(sql)
            datalist = cusr.fetchall()
            conn.close()
        except Exception as e:
            e.args[0]
        return datalist

    def show_order_list_bydate(self,startdate,enddate):
        sql = "select order_id,order_doc,p_name,reserve_num,futures_num,confirm_num,unit,order_date,ship_date,store from xb_order_tmp "
        if len(startdate) > 0 and len(enddate) > 0:
            sql +="  where order_date>='"+startdate+"' and order_date<='"+enddate+"' "
        try:
            mysql = MySQL()
            conn = mysql.connection_sql()
            cusr = conn.cursor()
            cusr.execute(sql)
            datalist = cusr.fetchall()
            conn.close()
        except Exception as e:
            e.args[0]
        return datalist

    def show_import_orderlist_byconf(self,startdate,enddate):

        sql = "select kalf_id,order_date,ship_date,kalf_doco,kalf_doc,xibei_store,kalf_code,xibei_code,kalf_name,kalf_unit," \
              "kalf_type,reserve_num,futures_num,confirm_num,receive_num,kalf_price,kalf_amnt,sys_date from kalf_order_list "
        if len(startdate) > 0 and len(enddate) > 0:
            sql +=" where order_date >='"+startdate+"' and order_date<='"+enddate+"' "
        print(sql)
        try:
            mysql = MySQL()
            conn = mysql.connection_sql()
            cusr = conn.cursor()
            cusr.execute(sql)
            datalist = cusr.fetchall()
            conn.close()
        except Exception as e:
            e.args[0]
        return datalist

    def show_import_orderlist(self):
        sql = "select kalf_id,order_date,ship_date,kalf_doco,kalf_doc,xibei_store,kalf_code,xibei_code,kalf_name,kalf_unit," \
              "kalf_type,reserve_num,futures_num,confirm_num,receive_num,kalf_price,kalf_amnt,sys_date from kalf_order_list "

        try:
            mysql = MySQL()
            conn = mysql.connection_sql()
            cusr = conn.cursor()
            cusr.execute(sql)
            datalist = cusr.fetchall()
            conn.close()
        except Exception as e:
            e.args[0]
        return datalist



class Product(object):

    def save_product_data(self,p_code,p_name,p_spec,p_unit,p_price,p_store):
        p_date = General.str_date
        sql ="insert into xb_product_list(p_code,p_name,p_spec,p_unit,p_price,p_date,p_store)VALUES ('"
        sql +=p_code+"','"+p_name+"','"+p_spec+"','"+p_unit+"','"+p_price+"','"+p_date+"','"+p_store+"')"
        try:
            mysql =MySQL()
            conn = mysql.connection_sql()
            cusr =conn.cursor()
            result = cusr.execute(sql)
            conn.commit()
            conn.close()
        except Exception as e:
            e.args[0]
        return result

    def show_product_lsit(self):
        sql ="select p_id,p_code,xb_code, p_name,p_spec,p_unit,p_price,p_prrce,p_store from xb_product_list where p_store='10001'"
        try:
            mysql =MySQL()
            conn = mysql.connection_sql()
            cusr =conn.cursor()
            cusr.execute(sql)
            datalist = cusr.fetchall()
            conn.close()
        except Exception as e:
            e.args[0]
        return datalist

    def update_product_price(self,id,price):
        sql ="update xb_product_list set p_prrce="+str(price)+"  where p_id="+str(id)
        try:
            mysql = MySQL()
            conn = mysql.connection_sql()
            cusr =conn.cursor()
            result =cusr.execute(sql)
            conn.commit()
            conn.close()
        except Exception as e:
            e.args[0]
        return result